package org.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.data.TableWithId;
import org.h2.jaqu.Db;
import org.h2.jaqu.Table;

/**
 *
 * @author Dmitriy.Krasnov
 */
public class GeneralDAO {
    private Db db;
    private Connection connection;
    private static GeneralDAO _self;
    private static String dbLocation = "~/data/OSDDB.db";
    private static String user = "sa";
    private static String password = "";    

    private GeneralDAO() {
        db = null;
        connection = null;
        db = Db.open("jdbc:h2:file:" + dbLocation, user, password);
        try {
            Class.forName("org.h2.Driver");
            connection = DriverManager.getConnection("jdbc:h2:file:" + dbLocation, user, password);
        } catch (SQLException ex) {
            Logger.getLogger(GeneralDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(GeneralDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Return next available id in table. Can work with all tables which have id field and getter for it.
     * 
     * @param table table in which should be founded available id
     * @return founded available id
     */
    public static Integer getAvailableId(TableWithId table) {
        int max = 0;
        List<Integer> list = GeneralDAO.getDb().from(table).orderBy(table.getId()).select(table.getId());
        if (list.size() > 0) {
            max = -1;
            for (Integer temp : list) {
                if (temp > max) {
                    max = temp;
                }
            }
        }
        return max + 1;
    }

    /**
     * Return total count of records in table.
     *
     * @param table to count records
     * @return total count of records in table
     */
    public static long getTotalCount(Table table) {
        return GeneralDAO.getDb().from(table).selectCount();
    }

    /**
     * Return connection to database if it already exists. If no, create connection and return it.
     * This connection should be in use when working with JaQu.
     * 
     * @return current database
     */
    public synchronized static Db getDb() {
        if (_self == null || _self.connection == null || _self.db == null) {
            _self = new GeneralDAO();
        }
        return _self.db;
    }

    /**
     * Return connection to database if it already exists. If no, create connection and return it.
     *
     * @return current database
     */
    public synchronized static Connection getConnection() {
        if (_self == null || _self.connection == null || _self.db == null) {
            _self = new GeneralDAO();
        }
        return _self.connection;
    }
    
    /**
     * Close current connection to database if it exists.
     */
    public synchronized static void closeConnection() {
        if (_self != null) {
            if (_self.db != null) {
                _self.db.close();
                _self.db = null;
            }
            if (_self.connection != null) {
                try {
                    _self.connection.close();
                } catch (SQLException ex) {
                    Logger.getLogger(GeneralDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                _self.connection = null;
            }
            _self = null;
        }
    }
    
    /**
     * Set current database location on hard drive.
     * 
     * @param location - database location on hard drive
     */
    public static void setDBLocation(String location){
        dbLocation = location;
    }
}
